package com.hist.dao;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import com.hist.entity.AllScore;
import com.hist.entity.Percent;
import com.hist.entity.Student;

@Mapper
public interface TeacherDao {
	
	  /*录入学生成绩*/
	  @Insert("insert into studentmanagement.score values(#{sno},#{cno},#{score})")
	  void insertScore(Integer sno,Integer cno,Double score);
	  
	 /*查看各分数段的学生人数以及百分比*/
		@Select("select studentmanagement.course.cno as cno,cname as cname,sum(case when 0<=score and score<60 then 1 else 0 end) as count1,"
	    		+ "sum(case when 0<=score and score<60  then 1 else 0 end)/count(score) as percent1,"
	    		+ "sum(case when 60<=score and score<=69 then 1 else 0 end) as count2,"
	    		+ "sum(case when 60<=score and score<=69 then 1 else 0 end)/count(score) as percent2,"
	    		+ "sum(case when 70<=score and score<=79 then 1 else 0 end) as count3,"
	    		+ "sum(case when 70<=score and score<=79 then 1 else 0 end)/count(score) as percent3,"
	    		+ "sum(case when 80<=score and score<89 then 1 else 0 end) as count4,"
	    		+ "sum(case when 80<=score and score<=89 then 1 else 0 end)/count(score) as percent4,"
	    		+ "sum(case when 90<=score and score<=100 then 1 else 0 end) as count5,"
	    		+ "sum(case when 90<=score and score<=100 then 1 else 0 end)/count(score) as percent5 from studentmanagement.score,studentmanagement.student,studentmanagement.course where studentmanagement.student.sno=studentmanagement.score.sno and studentmanagement.course.cno=studentmanagement.score.cno and smajor=#{smajor} and sgrade=#{sgrade} group by studentmanagement.score.cno ")
	           List<Percent> getpercent(String smajor,Integer sgrade);
	 /*查看全体学生成绩*/
	    @Select("select studentmanagement.score.sno,sname,smajor,sgrade,studentmanagement.score.cno,cname,score from studentmanagement.student,studentmanagement.score,studentmanagement.course where studentmanagement.student.sno=studentmanagement.score.sno and studentmanagement.course.cno=studentmanagement.score.cno")
	    List<AllScore> allScore();
	 /*按专业年级查看成绩*/
	    @Select("select studentmanagement.student.sno,sname,smajor,sgrade,studentmanagement.course.cno,cname,score from studentmanagement.student,studentmanagement.course,studentmanagement.score where studentmanagement.student.sno=studentmanagement.score.sno and studentmanagement.course.cno=studentmanagement.score.cno and smajor=#{smajor} and sgrade=#{sgrade}")
        List<AllScore> majorScore(String smajor,Integer sgrade);

		
}
